﻿using System.Collections.Generic;
using personalplaner.business.enums;
using personalplaner.business.stamm;
using System;
using System.Linq;
using personalplaner.core.sql;
using personalplaner.business.common;

namespace personalplaner.dataprovider.services
{
    public partial class PersonDataService
	{
		#region Get Methods

		/// <summary>
        /// Alle Personen des Typs with a empt row at top. 
        /// [prsid,vorname,nachname,strasse,stnr,postfach,plz,ort,telp,teln,email,anzeigename]
        /// </summary>
		/// <param name="settings"></param>
        /// <param name="mandid">Manant</param>
        /// <param name="typ">PersonTyp</param>
        /// <param name="prsid">person id</param>
        /// <param name="addEmptyRow">add empty row at top?</param>
        /// <returns></returns>
		public IEnumerable<Person> GetPersonen(DatabaseSettings settings, string mandid, enPersonTyp typ = enPersonTyp.Alle, int prsid = 0, bool addEmptyRow = false)
        {
            int t = (int)typ;

            var query = new Querry(settings,"get_personen");

            query.AddParameter("@mandid", mandid);
            query.AddParameter("@prsid", prsid);
            query.AddParameter("@typ", t);

            var table = query.ExecuteForDataTable();

            if (table.Rows.Count == 0)
                return null;

            try
            {
                var lst = new List<Person>(m_personenData(settings, table));

                if (addEmptyRow)
                {
                    lst.Insert(0, new Person());
                }

                return lst;
            }
            catch (Exception e)
            {
                throw new Exception("GetPersonen caused an error", e);
            }
		}

		public IEnumerable<Holiday> GetHolidays(DatabaseSettings settings, int prsid, int year)
		{
			var str = string.Format("select ferienid = FerienID, prsid = PrsId, von = Von, bis = Bis from Ferien where PrsId = {0} and (DATEPART(YYYY,Von) = {1} or DATEPART(YYYY,Bis) = {1}) order by Von, Bis", prsid, year);
			var table = SimpleQuerry.ExecuteForDataTable(settings, str);

			if (table.Rows.Count == 0)
				return null;

			try
			{
				return new List<Holiday>(m_holidayData(table));
			}
			catch (Exception e)
			{
				throw new Exception("GetHolidays caused an error", e);
			}
		}

		public IEnumerable<Holiday> GetHolidays(DatabaseSettings settings, int prsid, int year, int month)
		{
			var str = string.Format("select ferienid = FerienID, prsid = PrsId, von = Von, bis = Bis from Ferien where PrsId = {0} and ((DATEPART(YYYY,Von) = {1} and (DATEPART(MM,Von) = {2})) or (DATEPART(YYYY,Bis) = {1} and DATEPART(MM,Bis) = {2})) order by Von, Bis", prsid, year, month);
			var table = SimpleQuerry.ExecuteForDataTable(settings, str);

			if (table.Rows.Count == 0)
				return null;

			try
			{
				return new List<Holiday>(m_holidayData(table));
			}
			catch (Exception e)
			{
				throw new Exception("GetHolidays caused an error", e);
			}
		}

		#endregion

		#region Set Methods

		/// <summary>
		/// Saves a person. [prsid]
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="person"></param>
		/// <param name="mandid"></param>
		/// <param name="anstellungdatumvon"></param>
		/// <param name="anstellungdatumbis"></param>
		/// <param name="anstellungid"></param>
		/// <returns></returns>
		public int SavePerson(DatabaseSettings settings, Person person, string mandid, DateTime anstellungdatumvon, DateTime anstellungdatumbis, int anstellungid)
		{
			if (person.PersonTyp == enPersonTyp.Alle)
				person.PersonTyp = enPersonTyp.Stammperson;

			var query = new Querry(settings,"set_person");

			query.AddParameter("@mandid", mandid);
			query.AddParameter("@vorname", person.Name);
			query.AddParameter("@nachname", person.Nachname);
			query.AddParameter("@strasse", person.Strasse);
			query.AddParameter("@strassennr", person.StrassenNr);
			query.AddParameter("@postfach", person.Postfach);
			query.AddParameter("@plz", person.PLZ);
			query.AddParameter("@ort", person.Ort);
			query.AddParameter("@telnrp", person.TelefonPrivat);
			query.AddParameter("@telnrn", person.Natel);
			query.AddParameter("@email", person.EMail);
			query.AddParameter("@pensumvon", anstellungdatumvon.ToString("yyyyMMdd"));
			query.AddParameter("@pensumbis", anstellungdatumbis.ToString("yyyyMMdd"));
			query.AddParameter("@pensumprozent", person.ArbeitsPensum);
			query.AddParameter("@anstellungid", anstellungid);
			query.AddParameter("@persontyp", (int)person.PersonTyp);

			var table = query.ExecuteForDataTable(true);
			if (table.Rows.Count == 0)
				return 0;

			try
			{
                var p = new List<Person>(m_personenData(settings, table)).First();
				return p.PrsID;
			}
			catch (Exception e)
			{
				throw new Exception("SavePerson caused an error", e);
			}

		}

		/// <summary>
		/// adds a new holiday and returns the new ferienid
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="holiday"></param>
		/// <returns></returns>
		public int SetHoliday(DatabaseSettings settings, Holiday holiday)
		{
			var stp = new Querry(settings, "set_ferienzeit");

			stp.AddParameter("@prsid", holiday.PrsId);
			stp.AddParameter("@von", holiday.Von);
			stp.AddParameter("@bis", holiday.Bis);
			var retval = stp.AddParameter("@retval", 0, System.Data.ParameterDirection.Output);

			stp.Execute();

			return (int)retval.Value;
		}

		#endregion

		#region Update Methods

		/// <summary>
		/// Updates a person [prsid]
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="person"></param>
		/// <param name="anstellungid"></param>
		/// <returns></returns>
		public int UpdatePerson(DatabaseSettings settings, Person person/*, DateTime anstellungdatumvon, DateTime anstellungdatumbis, int anstellungid*/)
		{
			var query = new Querry(settings,"upd_Person");

			query.AddParameter("@vorname", person.Name);
			query.AddParameter("@nachname", person.Nachname);
			query.AddParameter("@strasse", person.Strasse);
			query.AddParameter("@strassennr", person.StrassenNr);
			query.AddParameter("@postfach", person.Postfach);
			query.AddParameter("@plz", person.PLZ);
			query.AddParameter("@ort", person.Ort);
			query.AddParameter("@telnrp", person.TelefonPrivat);
			query.AddParameter("@telnrn", person.Natel);
			query.AddParameter("@email", person.EMail);
            //query.AddParameter("@pensumvon", anstellungdatumvon.ToString("yyyyMMdd"));
            //query.AddParameter("@pensumbis", anstellungdatumbis.ToString("yyyyMMdd"));
			query.AddParameter("@pensumprozent", person.ArbeitsPensum);// pensum); //CWA: TEST THIs
            //query.AddParameter("@anstellungid", anstellungid);
			query.AddParameter("@prsid", person.PrsID);

			var table = query.ExecuteForDataTable(true);
			if (table.Rows.Count == 0)
				return 0;

			try
			{
				var p = new List<Person>(m_personenData(settings, table)).First();
				return p.PrsID;
			}
			catch (Exception e)
			{
				throw new Exception("UpdatePerson caused an error", e);
			}

		}

		#endregion

		#region Delete Methods

		public bool DeletePerson(DatabaseSettings settings, Person person)
		{
			var query = new Querry(settings, "del_person");

			query.AddParameter("@prsid", person.PrsID);

			return query.Execute(true);
		}

		public bool DeleteHoliday(DatabaseSettings settings, int ferienid)
		{
			var str = string.Format("delete from Ferien where FerienID = {0}", ferienid);
			return SimpleQuerry.Execute(settings, str);
		}

		#endregion

		/*
        /// <summary>
        /// Creates a DataView with a specific abteilung from a Person. abteilungid,pensum,von,bis,name
        /// </summary>
        /// <param name="prsid">prsid</param>
        /// <param name="abteilungid">abteilungid</param>
        /// <returns>abteilungid,pensum,von,bis,name</returns>
        public static DataView GetPersonAbteilung(int prsid, int abteilungid)
        {
            Querry query = new Querry();
            query.SetSTPName("get_arbeitspensum");
            query.AddParameter("@prsid", prsid);
            query.AddParameter("@abteilungid", abteilungid);
            return query.ExecuteForDataView();
        }

        /// <summary>
        /// Creates a DataView with all abteilungen of a Person. abteilungid,pensum,von,bis,name
        /// </summary>
        /// <param name="prsid">PrsID</param>
        /// <returns>abteilungid,pensum,von,bis</returns>
        public static DataView GetPersonAbteilung(int prsid)
        {
            return GetPersonAbteilung(prsid, 0);
        }


        /// <summary>
        /// Saves a person. [prsid]
        /// </summary>
        /// <param name="vorname"></param>
        /// <param name="nachname"></param>
        /// <param name="strasse"></param>
        /// <param name="strassennr"></param>
        /// <param name="postfach"></param>
        /// <param name="plz"></param>
        /// <param name="ort"></param>
        /// <param name="telefonp"></param>
        /// <param name="natel"></param>
        /// <param name="email"></param>
        /// <param name="anstellungdatumvon">normal datetime</param>
        /// <param name="anstellungdatumbis">normal datetime</param>
        /// <param name="pensum"></param>
        /// <param name="anstellungid"></param>
        /// <param name="ptyp"></param>
        /// <returns>prsid</returns>
        public static int SavePerson(string vorname, string nachname, string strasse, string strassennr,
            string postfach, string plz, string ort, string telefonp,
            string natel, string email, string anstellungdatumvon, string anstellungdatumbis,
            int pensum, int anstellungid, enPersonTyp ptyp, string mandid)
        {
            if (ptyp == enPersonTyp.Alle)
                ptyp = enPersonTyp.Stammperson;

            Querry query = new Querry();
            query.SetSTPName("set_person");

            query.AddParameter("@mandid", mandid);
            query.AddParameter("@vorname", vorname);
            query.AddParameter("@nachname", nachname);
            query.AddParameter("@strasse", strasse);
            query.AddParameter("@strassennr", strassennr);
            query.AddParameter("@postfach", postfach);
            query.AddParameter("@plz", plz);
            query.AddParameter("@ort", ort);
            query.AddParameter("@telnrp", telefonp);
            query.AddParameter("@telnrn", natel);
            query.AddParameter("@email", email);
            query.AddParameter("@pensumvon", DateUtil.ConvertDateToSQL(anstellungdatumvon));
            query.AddParameter("@pensumbis", DateUtil.ConvertDateToSQL(anstellungdatumbis));
            query.AddParameter("@pensumprozent", pensum);
            query.AddParameter("@anstellungid", anstellungid);
            query.AddParameter("@persontyp", (int)ptyp);

            int prsid = 0;
            DataView dv = query.ExecuteForDataView(true);
            if (dv.Count > 0)
                try
                {
                    prsid = int.Parse(dv[0].Row[0].ToString());
                }
                catch { }
            dv.Dispose();

            return prsid;

        }

        /// <summary>
        /// Updates a person [prsid]
        /// </summary>
        /// <param name="prsid"></param>
        /// <param name="vorname"></param>
        /// <param name="nachname"></param>
        /// <param name="strasse"></param>
        /// <param name="strassennr"></param>
        /// <param name="postfach"></param>
        /// <param name="plz"></param>
        /// <param name="ort"></param>
        /// <param name="telefonp"></param>
        /// <param name="natel"></param>
        /// <param name="email"></param>
        /// <param name="anstellungdatumvon"></param>
        /// <param name="anstellungdatumbis"></param>
        /// <param name="pensum"></param>
        /// <param name="anstellungid"></param>
        /// <param name="ptyp"></param>
        /// <returns>prsid</returns>
        public static int UpdatePerson(int prsid, string vorname, string nachname, string strasse, string strassennr,
            string postfach, string plz, string ort, string telefonp,
            string natel, string email, string anstellungdatumvon, string anstellungdatumbis,
            int pensum, int anstellungid)
        {
            Querry query = new Querry();
            query.SetSTPName("upd_Person");

            query.AddParameter("@vorname", vorname);
            query.AddParameter("@nachname", nachname);
            query.AddParameter("@strasse", strasse);
            query.AddParameter("@strassennr", strassennr);
            query.AddParameter("@postfach", postfach);
            query.AddParameter("@plz", plz);
            query.AddParameter("@ort", ort);
            query.AddParameter("@telnrp", telefonp);
            query.AddParameter("@telnrn", natel);
            query.AddParameter("@email", email);
            query.AddParameter("@pensumvon", DateUtil.ConvertDateToSQL(anstellungdatumvon));
            query.AddParameter("@pensumbis", DateUtil.ConvertDateToSQL(anstellungdatumbis));
            query.AddParameter("@pensumprozent", pensum);
            query.AddParameter("@anstellungid", anstellungid);
            query.AddParameter("@prsid", prsid);

            DataView dv = query.ExecuteForDataView(true);
            if (dv.Count > 0)
                prsid = int.Parse(dv[0].Row[0].ToString());
            dv.Dispose();

            return prsid;

        }

        public static void SetAbteilung(int prsid, int abteilungid, string von, string bis, int prozent)
        {
            Querry stp = new Querry();
            stp.SetSTPName("set_prsabteilung");

            stp.AddParameter("@abteilungid", abteilungid);
            stp.AddParameter("@prsid", prsid);
            stp.AddParameter("@von", DateUtil.ConvertDateToSQL(von));
            stp.AddParameter("@bis", DateUtil.ConvertDateToSQL(bis));
            stp.AddParameter("@prozent", prozent);

            stp.Execute(true);
        }

        public static void RemovePersonAbteilung(int prsid, int abteilungid, string datevon, string datebis)
        {
            Querry stp = new Querry();
            stp.SetSTPName("upd_prsabteilung");

            stp.AddParameter("@abteilungid", abteilungid);
            stp.AddParameter("@prsid", prsid);
            stp.AddParameter("@vonnew", DateUtil.ConvertDateToSQL(datevon));
            stp.AddParameter("@bisnew", DateUtil.ConvertDateToSQL(datebis));

            stp.Execute(true);
        }

        public static void SetAnstellungToPerson(int prsid, int anstellungid, string von, string bis)
        {
            Querry stp = new Querry();
            stp.SetSTPName("upd_prsanstellung");
            stp.AddParameter("@prsid", prsid);
            stp.AddParameter("@anstellungid", anstellungid);
            stp.AddParameter("@von", DateUtil.ConvertDateToSQL(von));
            stp.AddParameter("@bis", DateUtil.ConvertDateToSQL(bis));

            stp.Execute(true);
        }

        public static bool SetPersonGesamtPensum(int prsid, int pensum, string von, string bis)
        {
            Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, "Pensum ändern von Person:" + prsid + " Prozent:" + pensum + " Von:" + von + " Bis:" + bis);

            Querry stp = new Querry("set_newpensum");

            stp.AddParameter("@prsid", prsid);
            stp.AddParameter("@pensum", pensum);
            stp.AddParameter("@von", DateUtil.ConvertDateToSQL(von));
            stp.AddParameter("@bis", DateUtil.ConvertDateToSQL(bis));

            return stp.Execute(true);
        }

        public static bool PersonPensumKuendigung(int prsid, string datum)
        {
            Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, "Kündigung von Person:" + prsid + " Am:" + datum);

            Querry stp = new Querry("set_personkuendigung");

            stp.AddParameter("@prsid", prsid);
            stp.AddParameter("@bis", DateUtil.ConvertDateToSQL(datum));

            return stp.Execute(true);
        }

        public static DataView GetFerien(int prsid, int year)
        {
            return SimpleQuerry.ExecuteForDataView("select FerienID,PrsId,Von,Bis from Ferien where PrsId = " + prsid + " and (DATEPART(YYYY,Von) = " + year + " or DATEPART(YYYY,Bis) = " + year + ") order by Von,Bis");
        }

        public static void DeleteFerien(int ferienid)
        {
            SimpleQuerry.Execute("delete from Ferien where FerienID = " + ferienid);
        }

        /// <summary>
        /// [PrsId,AbteilungID,Name,Von,Bis,Prozent,Status,Statustext]
        /// </summary>
        /// <param name="prsid"></param>
        /// <returns></returns>
        public static DataView GetPersonAbteilungHistory(int prsid)
        {
            return SimpleQuerry.ExecuteForDataView("select PrsId,a.AbteilungID,a.Name,Von,Bis,Prozent,Status,Statustext=case when Status=1 then 'Aktiv' else 'Inaktiv'end from PersonalAbteilung pa inner join Abteilung a on pa.AbteilungID=a.AbteilungID where PrsId = " + prsid + " order by Von desc, Bis");
        }

        /// <summary>
        /// [PrsID,Von,Bis,Prozent,Status,Statustext]
        /// </summary>
        /// <param name="prsid"></param>
        /// <returns></returns>
        public static DataView GetPersonArbeitspensumHistory(int prsid)
        {
            return SimpleQuerry.ExecuteForDataView("select PrsID,Von,Bis,Prozent,Status,Statustext=case when Status=1 then 'Aktiv' else 'Inaktiv'end from Arbeitspensum where PrsId = " + prsid);
        }
         
         
        */
    }
}
